package model;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

import beans.MyEventBean;
import beans.UserBean;


public class MyEventDAO {
	static Connection currentCon = null;
	static ResultSet rs = null;


	public static ArrayList<MyEventBean> gethostupcoming(UserBean user) {

		Statement stmt = null;
		String username = user.getEmail();
		String searchQuery = null; 
		searchQuery = "select name, starttimestamp, numberguestspots, numberparticipants from event where host ='" + username + "'and endtime > CURRENT_TIMESTAMP" ;
		ArrayList<MyEventBean> hostupcomingList = new ArrayList<MyEventBean>();

		try {
			currentCon = ConnectionManager.getConnection();
			stmt = currentCon.createStatement();
			rs = stmt.executeQuery(searchQuery);

			while (rs.next()) {
				MyEventBean myevent = new MyEventBean();

				myevent.setEventname(rs.getString(1));
				myevent.setStamp(rs.getTimestamp(2));
				myevent.setnGuestspots(rs.getInt(3));
				myevent.setnParticipants(rs.getInt(4));

				hostupcomingList.add(myevent);
			}
		}

		catch (Exception ex) {
			System.out.println("FAIL!: An Exception has occurred on gethostupcoming! " + ex);
		}

		finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception e) {
				}
				rs = null;
			}

			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception e) {
				}
				stmt = null;
			}

			if (currentCon != null) {
				try {
					currentCon.close();
				} catch (Exception e) {
				}

				currentCon = null;
			}
		}
		return hostupcomingList;
	}


	public static ArrayList<MyEventBean> gethostpast(UserBean user) {

		Statement stmt = null;

		String username = user.getEmail();
		String searchQuery = null; 

		searchQuery = "select name, starttimestamp, numberguestspots, numberparticipants, avg(eventrating) from (select name, starttimestamp, numberguestspots, numberparticipants from event where host ='" + username + "' and endtime < CURRENT_TIMESTAMP) as table1 natural join (select host, avg(hostrating) as eventrating from attend group by host) as table2 group by table1.name, table1.starttimestamp,table1.numberguestspots,table1.numberparticipants";

		ArrayList<MyEventBean> hostpastList = new ArrayList<MyEventBean>();


		try {
			currentCon = ConnectionManager.getConnection();
			stmt = currentCon.createStatement();
			rs = stmt.executeQuery(searchQuery);

			while (rs.next()) {
				MyEventBean myevent = new MyEventBean();

				myevent.setEventname(rs.getString(1));
				myevent.setStamp(rs.getTimestamp(2));
				myevent.setnGuestspots(rs.getInt(3));
				myevent.setnParticipants(rs.getInt(4));
				myevent.setRating(rs.getInt(5));
				hostpastList.add(myevent);
			}
		}

		catch (Exception ex) {
			System.out.println("FAIL!: An Exception has occurred on gethostpast! " + ex);
		}

		finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception e) {
				}
				rs = null;
			}

			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception e) {
				}
				stmt = null;
			}

			if (currentCon != null) {
				try {
					currentCon.close();
				} catch (Exception e) {
				}

				currentCon = null;
			}
		}
		return hostpastList; 		
	}

	public static ArrayList<MyEventBean> getguestupcoming(UserBean user) {

		Statement stmt = null;

		String username = user.getEmail();
		String searchQuery = null; 

		searchQuery = " select name, starttimestamp, numberguestspots, numberparticipants, host, hostname,  rating, guestrating as myrating from(select * from (select E.name, E.starttimestamp, E.host, E.numberguestspots, E.numberparticipants, A.guestrating from attend as A join event as E on A.host=E.host and A.eventtimestamp=E.starttimestamp where guest='"+ username +"' and E.endtime > CURRENT_TIMESTAMP) as table1 natural join (select host, avg(hostrating) as rating from attend group by host) as table2) as A, (select name as hostname, email from users) as B where A.host=B.email";

		ArrayList<MyEventBean> guestupcomingList = new ArrayList<MyEventBean>();

		try {
			currentCon = ConnectionManager.getConnection();
			stmt = currentCon.createStatement();
			rs = stmt.executeQuery(searchQuery);

			while (rs.next()) {
				MyEventBean myevent = new MyEventBean();

				myevent.setEventname(rs.getString(1));
				myevent.setStamp(rs.getTimestamp(2));
				myevent.setnGuestspots(rs.getInt(3));
				myevent.setnParticipants(rs.getInt(4));
				myevent.setHost(rs.getString(5));
				myevent.sethostName(rs.getString(6));
				myevent.sethostavgRating(rs.getInt(7));
				guestupcomingList.add(myevent);

			}
		}

		catch (Exception ex) {
			System.out.println("FAIL!: An Exception has occurred on getguestupcoming! " + ex);
		}

		finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception e) {
				}
				rs = null;
			}

			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception e) {
				}
				stmt = null;
			}

			if (currentCon != null) {
				try {
					currentCon.close();
				} catch (Exception e) {
				}

				currentCon = null;
			}
		}


		return guestupcomingList;


	}

	public static ArrayList<MyEventBean> getguestpast(UserBean user) {

		Statement stmt = null;

		String username = user.getEmail();
		String searchQuery = null; 


		searchQuery = "select name, starttimestamp, numberguestspots, numberparticipants, host, hostname,  rating, guestrating as myrating from(select * from (select E.name, E.starttimestamp, E.host, E.numberguestspots, E.numberparticipants, A.guestrating from attend as A join event as E on A.host=E.host and A.eventtimestamp=E.starttimestamp where guest='"+ username +"' and E.endtime < CURRENT_TIMESTAMP) as table1 natural join (select host, avg(hostrating) as rating from attend group by host) as table2) as A, (select name as hostname, email from users) as B where A.host=B.email";
		ArrayList<MyEventBean> guestpastList = new ArrayList<MyEventBean>();

		try {
			currentCon = ConnectionManager.getConnection();
			stmt = currentCon.createStatement();
			rs = stmt.executeQuery(searchQuery);

			while (rs.next()) {
				MyEventBean myevent = new MyEventBean();

				myevent.setEventname(rs.getString(1));
				myevent.setStamp(rs.getTimestamp(2));
				myevent.setnGuestspots(rs.getInt(3));
				myevent.setnParticipants(rs.getInt(4));
				myevent.setHost(rs.getString(5));
				myevent.sethostName(rs.getString(6));
				myevent.sethostavgRating(rs.getInt(7));
				myevent.setRating(rs.getInt(8));
				guestpastList.add(myevent);
			}
		}

		catch (Exception ex) {
			System.out.println("FAIL!: An Exception has occurred on getguestpast! " + ex);
		}

		finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (Exception e) {
				}
				rs = null;
			}

			if (stmt != null) {
				try {
					stmt.close();
				} catch (Exception e) {
				}
				stmt = null;
			}

			if (currentCon != null) {
				try {
					currentCon.close();
				} catch (Exception e) {
				}

				currentCon = null;
			}
		}	 			
		return guestpastList;	 		
	}
}
